Skip Headers

Oracle® XML DB Developer's Guide 10g Release 2 (10.2) Part Number B14259-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 30 Importing and Exporting XMLType Tables

This chapter describes how you can import and export `XMLType` tables for use with Oracle XML DB.

It contains the following sections:

- Overview of IMPORT/EXPORT Support in Oracle XML DB

- Non-Schema-Based XMLType Tables and Columns

- XML Schema-Based XMLType Tables

- Using Transportable Tablespaces with Oracle XML DB

- Resources and Foldering Do Not Fully Support IMPORT/EXPORT

- Repository Metadata is Not Exported During a Full Database Export

- Importing and Exporting with Different Character Sets

- IMPORT/EXPORT Syntax and Examples

## Overview of IMPORT/EXPORT Support in Oracle XML DB

Oracle XML DB supports import and export of `XMLType` tables and columns that store XML data and are based on a registered XML schema. You can import and export tables, whether or not they are based on XML schemas.

## Non-Schema-Based XMLType Tables and Columns

Data from `XMLType` tables and columns that are not associated with an XML schema can be imported and exported in a manner similar to LOB columns. The export dump file stores the XML text.

## XML Schema-Based XMLType Tables

Oracle Database supports the import and export of XML schema-based `XMLType` tables. An `XMLType` table depends on the XML schema used to define it. Similarly the XML schema has dependencies on the SQL object types created or specified for it. Thus, exporting a user with XML schema-based `XMLType` tables, consists of the following steps:

1. Exporting SQL Types During XML Schema Registration. As a part of the XML schema registration process, SQL types can be created. These SQL types are exported as a part of `CREATE TYPE` statement along with their OIDs.

2. Exporting XML Schemas. After all the types are exported, XML schemas are exported as XML text, as part of a call to PL/SQL procedure `DBMS_XMLSCHEMA.registerSchema`.

3. Exporting XML Tables. The next step is to export the tables. Export of each table consists of two steps:

   1. The table definition is exported as a part of the `CREATE TABLE` statement along with the table OID.

   2. The data in the table is exported as XML text. Note that data for out-of-line tables is not explicitly exported. It is exported as a part of the data for the parent table.

      Note: OCTs and nested tables are not exported separately. They are exported as parts of the parent table.

### Guidelines for Exporting Hierarchy-Enabled Tables

The following describes guidelines for exporting hierarchy-enabled tables:

- The row-level security (RLS) policies and path-index triggers are not exported for hierarchy-enabled tables: when these tables are imported, they are not hierarchy-enabled.

- Hidden columns `ACLOID` and `OWNERID` are not exported for these tables. In an imported database the values of these columns could be different, so they should be re-initialized.

## Using Transportable Tablespaces with Oracle XML DB

You can move XML schema-based or non-schema-based data from one database to another using the transportable tablespace feature. This is generally very fast, because it involves only copying the tablespace and recreating the tablespace metadata.

When exporting:

- Hierachy information is lost (see "Guidelines for Exporting Hierarchy-Enabled Tables").

- Ensure that the set of tablespaces to be exported is read-only and self-contained. You can check that it is self-contained by using `DBMS_TTS.transport_set_check`.

- After exporting, run the RMAN tool to take care of any endian issues.

- Use `ignore=y` when importing, if you do not want an error to be raised when an XML schema referenced by the imported data already exists and is different.

When importing, any XML schemas referenced by the data to be imported are also imported. If such a schema already exists in the target database, then an error is signaled if any of the following is true:

- `ignore=n` is specified in the import utility

- `ignore=y` is specified in the import utility and the imported schema is not identical to the existing schema in the target database.

## Resources and Foldering Do Not Fully Support IMPORT/EXPORT

Oracle XML DB supports a foldering mechanism that uses path names and URIs to refer to data (repository resources), rather than table names, column names, and so on. This foldering mechanism is not entirely supported using IMPORT/EXPORT.

However, for resources based on a registered XML schema, the `XMLType` tables storing the data can be imported and exported. During export, only the XML data is exported; the relationship in the Oracle XML DB foldering hierarchy is lost.

## Repository Metadata is Not Exported During a Full Database Export

Oracle XML DB stores the metadata (and data unrelated to XML Schema) for Oracle XML DB Repository in the `XDB` database-user schema. Because Oracle Database does not support the export of the repository structure, these metadata tables and structures are not exported during a full database export.

The entire `XDB` user schema is skipped during a full database export and any database objects owned by `XDB` are not exported.

## Importing and Exporting with Different Character Sets

As with other database objects, XML data is exported in the character set of the exporting server. During import, the data gets converted to the character set of the importing server.

## IMPORT/EXPORT Syntax and Examples

The `IMPORT/EXPORT` syntax and description are described in Oracle Database Utilities. This chapter includes additional guidelines and examples for using IMPORT/EXPORT with `XMLType` data.

IMPORT/EXPORT Example

Assumptions: The examples here assume that you are using a database with the following features:

- Two users, `U1` and `U2`

- `U1` has a registered local XML schema, `SL1`. This also created a default table `TL1`

- `U1` has a registered global XML schema, `SG1`. This also created a default table `TG1`

- `U2` has created table `TG2` based on schema `SG1`

### User Level Import/Export

Example 30-1 Exporting XMLType Data

    exp sytem/manager file=file1 owner=U1

This exports the following:

- Any types that were generated during schema registration of schemas `SL1` and `SG1`

- Schemas `SL1` and `SG1`

- Tables `TL1` and `TG1` and any other tables that were generated during schema registration of schemas `SL1` and `SG1`

- Any data in any of the preceding tables

Example 30-2 Exporting XMLType Tables

    exp sytem/manager file=file2 owner=U2

This exports the following:

- Table `TG2` and any other tables that were generated during creation of `TG2`

- Any data in any of the preceding tables

Note: This does not export Schema `SG1` or any types that were created during the registration of schema `SG1`.

Example 30-3 Importing Data from a File

    imp system/manager file=file1 fromuser=U1 touser=newuser

This imports all the data in file1.dmp to schema newuser.

Import fails if the `FROMUSER` object types and object tables already exist on the target system. See "Considerations When Importing Database Objects" in Database Utilities.

### Table Mode Export

An `XMLType` table has a dependency on the XML schema that was used to define it. Similarly the XML schema has dependencies on the SQL object types created or specified for it. Importing an `XMLType` table requires the existence of the XML schema and the SQL object types. When a `TABLE` mode export is used, only the table related metadata and data are exported. To be able to import this data successfully, the user needs to ensure that both the XML schema and object types have been created.

Example 30-4 Exporting XML Data in TABLE Mode

    exp SYSTEM/MANAGER file=expdat.dmp tables=U1.TG1

This exports:

- Table `TG1` and any nested tables that were generated during creation of `TG1`

- Any data in any of the preceding tables

  Note: This does not export schema `SG1` or any types that were created during the registration of schema `SG1`.

Example 30-5 Importing XML Data in TABLE Mode

    imp SYSTEM/MANAGER file=expdat.dmp fromuser=U1 touser=U2 tables=TG1

This creates table `TG1` for user `U2`, because `U2` already has access to the global schema `SG1` and the types that it depends on.

Import fails if the `FROMUSER` object types and object tables already exist on the target system. See "Considerations When Importing Database Objects" in Database Utilities.